﻿using log4net;
using Microsoft.AspNetCore.Mvc;
using MySqlX.XDevAPI.Common;
using NetTaste;
using Newtonsoft.Json;
using NPOI.XSSF.UserModel;

using SqlSugar;
using System.Data;
using System.Dynamic;
using System.IO.Compression;
using System.Text.Json.Serialization;
using System.Text.Json;
using ztDataNet.db;
using DbType = SqlSugar.DbType;
using System.Net.Http;
using System.Text;
using MailKit.Net.Imap;
using MailKit;
using Dm;

namespace ztDataNet.Controllers
{
    /// <summary>
    /// 公共操作类
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class mainControllers : ControllerBase
    {
        private readonly ISqlSugarClient db;
        private readonly IHttpClientFactory _httpClientFactory;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="db"></param>
        public mainControllers(ISqlSugarClient db, IHttpClientFactory httpClientFactory)
        {
            this.db = db;
            _httpClientFactory = httpClientFactory;

        }

        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("ExecuteQuery")]
        public IActionResult ExecuteQuery([FromBody] QueryRequest request)
        {
            if (string.IsNullOrWhiteSpace(request.SqlQuery))
            {
                return BadRequest("SQL query is empty.");
            }

            try
            {
                // 执行查询
                var result = db.Ado.SqlQuery<dynamic>(request.SqlQuery);
                
                return Ok(result);
            }
            catch (Exception ex)
            {
                // 处理异常
                return StatusCode(500, $"Error executing query: {ex.Message}");
            }
        }
        /// <summary>
        /// 生成雪花ID
        /// </summary>
        /// <returns></returns>
        [HttpGet("ExecuteReturnSnowflakeId")]
        public IActionResult ExecuteReturnSnowflakeId()
        {
            var id = SnowFlakeSingle.Instance.NextId();

            return Ok(id);
        }
        private DbType GetDbType(string dbType)
        {
            return dbType.ToLower() switch
            {
                "mysql" => DbType.MySql,
                "sqlserver" => DbType.SqlServer,
                "postgresql" => DbType.PostgreSQL,
                "sqlite" => DbType.Sqlite,
                "oracle" => DbType.Oracle,
                _ => throw new ArgumentException($"Invalid database type: {dbType}")
            };
        }
        private void DeleteFilesInDirectory(string targetDir)
        {
            DirectoryInfo dir = new DirectoryInfo(targetDir);

            if (!dir.Exists)
            {
                throw new DirectoryNotFoundException(
                    "Directory not found: " + targetDir);
            }

            foreach (FileInfo file in dir.GetFiles())
            {
                file.Delete();
            }
        }
        private List<string> GetFileNamesInDirectory(string targetDir)
        {
            DirectoryInfo dir = new DirectoryInfo(targetDir);

            if (!dir.Exists)
            {
                throw new DirectoryNotFoundException(
                    "Directory not found: " + targetDir);
            }

            List<string> fileNames = new List<string>();
            foreach (FileInfo file in dir.GetFiles())
            {
                fileNames.Add(file.Name);
            }

            return fileNames;
        }
        /// <summary>
        /// 生成带有SqlSugar特性的实体
        /// </summary>
        /// <returns></returns>
        [HttpGet("DbFirst")]
        public IActionResult DbFirst()
        {
            var dbEntities = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "Server=119.45.240.39;Port=3306;Database=ztdata;Uid=ztData;Pwd=bPXREBwmFcMLA3sG;",
                DbType = GetDbType("mysql"),
                IsAutoCloseConnection = true,
                InitKeyType = InitKeyType.Attribute
            });
            DeleteFilesInDirectory(Path.Combine(Directory.GetCurrentDirectory(), "temp"));
            var baseDirectory = Directory.GetCurrentDirectory();
            var entitiesDirectory = Path.Combine(baseDirectory, "temp", "entities");
            var zipDirectory = Path.Combine(baseDirectory, "temp");
            dbEntities.DbFirst.IsCreateAttribute().CreateClassFile(entitiesDirectory, "entities");
            List<string> fileNames = GetFileNamesInDirectory(Path.Combine(Directory.GetCurrentDirectory(), "temp/entities"));
            var zipPath = Path.Combine(zipDirectory, "entities.zip");
            // 检查zip文件是否已经存在，如果存在则删除
            if (System.IO.File.Exists(zipPath))
            {
                System.IO.File.Delete(zipPath);
            }
            ZipFile.CreateFromDirectory(entitiesDirectory, zipPath);

            var mimeType = "application/zip";
            var fileDownloadName = "entities.zip";

            var fileBytes = System.IO.File.ReadAllBytes(zipPath);
            
            var result = new
            {
                File = File(fileBytes, mimeType, fileDownloadName),
                FileNames = fileNames
            };

            return Ok(result);
        }

        /// <summary>
        /// 执行其他
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [HttpPost("ExecuteCommand")]
        public IActionResult ExecuteCommand([FromBody] CommandRequest request)
        {
            if (string.IsNullOrWhiteSpace(request.SqlCommand))
            {
                return BadRequest("SQL command is empty.");
            }

            try
            {
                int i = db.Ado.ExecuteCommand(request.SqlCommand);
                
                return Ok(i);
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"Error executing command: {ex.Message}");
            }
        }

        /// <summary>
        /// 上传excel文件
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost("ExecuteUpload")]
        public async Task<IActionResult> ExecuteUpload(IFormFile file)
        {
            if (file == null || file.Length == 0)
                return Content("file not selected");

            var list = new List<Dictionary<string, object>>();
            var mergedCells = new List<MergedCell>();

            using (var stream = new MemoryStream())
            {
                await file.CopyToAsync(stream);
                stream.Position = 0;
                var workbook = new XSSFWorkbook(stream);
                var sheet = workbook.GetSheetAt(0);
                var headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;

                // 处理合并单元格
                for (int i = 0; i < sheet.NumMergedRegions; i++)
                {
                    var mergedRegion = sheet.GetMergedRegion(i);
                    var cell = sheet.GetRow(mergedRegion.FirstRow).GetCell(mergedRegion.FirstColumn);
                    var cellValue = cell?.ToString() ?? string.Empty;

                    mergedCells.Add(new MergedCell
                    {
                        StartRow = mergedRegion.FirstRow,
                        EndRow = mergedRegion.LastRow,
                        StartColumn = mergedRegion.FirstColumn,
                        EndColumn = mergedRegion.LastColumn,
                        Value = cellValue
                    });
                }

                // 读取数据
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue;
                    var dict = new Dictionary<string, object>();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        var cell = row.GetCell(j);
                        var cellValue = cell?.ToString() ?? string.Empty;

                        var header = headerRow.GetCell(j)?.ToString();
                        if (header != null)
                        {
                            dict[header] = cellValue;
                        }
                    }
                    list.Add(dict);
                }
            }

            var result = new
            {
                Data = list,
                MergedCells = mergedCells
            };

            return Ok(JsonConvert.SerializeObject(result));
        }

        /// <summary>
        /// 每日发送
        /// </summary>
        /// <returns></returns>
        [HttpGet("GetMenu")]
        public async Task<ActionResult<IEnumerable<menu>>> GetMenu()
        {
            var topLevelMenus = await db.Queryable<menu>().Where(x => x.pid == 0).OrderBy(t => t.inputtime, OrderByType.Desc).ToListAsync();

            List<dynamic> menuList = new List<dynamic>();

            foreach (var m in topLevelMenus)
            {
                var data = db.Queryable<menu>().Where(x => x.pid == m.id);
                dynamic menuWithSub = new ExpandoObject();
                menuWithSub.Menu = m;
                menuWithSub.Count =  data.Count();
                menuWithSub.SubMenus = await data.OrderBy(t => t.inputtime, OrderByType.Desc).ToListAsync();
               
                menuList.Add(menuWithSub);
            }

            return Ok(menuList);

        }

        /// <summary>
        /// 去水印
        /// </summary>
        /// <returns></returns>
        [HttpGet("GotoWatermark")]
        public async Task<IActionResult> GotoWatermark(string url)
        {

            var res = utils.GotoWatermark(url);

            var options = new JsonSerializerOptions
            {
                ReferenceHandler = ReferenceHandler.Preserve
            };
           ;

            return Ok(System.Text.Json.JsonSerializer.Serialize(res, options));

        }
        /// <summary>
        /// 每日发送
        /// </summary>
        /// <returns></returns>
        [HttpGet("GetFY")]
        public async Task<ActionResult<IEnumerable<menu>>> GetFY(string input,string target)
        {
           

            return Ok(new TextTranslate().Translate(input, target));

        }

        [HttpPost("CallApiAsync")]
        public async Task<IActionResult> CallApiAsync()
        {
            string url = Request.Form["url"];
            string data = Request.Form["data"];
            string headers = Request.Form["headers"];
            string method = Request.Form["method"];
            var r = await utils.CallApiAsync(url,data,headers,method);
                return Ok(r);
           
        }

        [HttpPost("CallApiAsync2")]
        public async Task<IActionResult> CallApiAsync2()
        {
            var r = await utils.CallApiAsync("https://oa.api2d.net/v1/chat/completions","{\"messages\":[{\"role\":\"user\",\"content\":\"你好\"},{\"role\":\"assistant\",\"content\":\"正在思考请稍后...\"},{\"role\":\"user\",\"content\":\"你好\"},{\"role\":\"assistant\",\"content\":\"\"}],\"model\":\"gpt-4-1106-preview\",\"stream\":false,\"top_p\":1,\"temperature\":0.3,\"presence_penalty\":0.6,\"frequency_penalty\":0}", "Authorization:Bearer fk213277-R4qHL4g28YI8KGGQAhcfn0ZeZwOkM5RU;Content-Type:application/json", "POST");
            return Ok(r);

        }

        [HttpPost("PostAsync")]
        public async Task<IActionResult> PostAsync([FromBody] object data)
        {
            var httpClient = _httpClientFactory.CreateClient();
            var requestContent = new StringContent(System.Text.Json.JsonSerializer.Serialize(data), Encoding.UTF8, "application/json");

            // 创建HttpRequestMessage以自定义请求
            var request = new HttpRequestMessage(HttpMethod.Post, "https://oa.api2d.net/v1/chat/completions")
            {
                Content = requestContent
            };

            // 添加Authorization头部
            request.Headers.Add("Authorization", "Bearer fk213277-R4qHL4g28YI8KGGQAhcfn0ZeZwOkM5RU"); // 替换为实际的API密钥

            // 发送请求
            var response = await httpClient.SendAsync(request);

            if (response.IsSuccessStatusCode)
            {
                return File(await response.Content.ReadAsStreamAsync(), "application/json");
            }
            else
            {
                return StatusCode((int)response.StatusCode, await response.Content.ReadAsStringAsync());
            }
        }

        [HttpGet("GetMails")]
        public async Task<IActionResult> GetMails()
        {
            var emails = new List<Email>();
            using (var client = new ImapClient(new ProtocolLogger("imap.log"))) // for debugging
            {
                client.Connect("imap.qq.com", 993, true);
                //client.Connect("pop.163.com", 995, true);
                // Note: since we don't have an OAuth2 token, disable
                // the XOAUTH2 authentication mechanism.
                client.AuthenticationMechanisms.Remove("XOAUTH2");
                //UDUQREHMCEOSOQPN
                //wejqwyblnryjbigf

                client.Authenticate("406883548@qq.com", "wejqwyblnryjbigf"); // replace with your email and authorization code
               // client.Authenticate("zhangteng0208@163.com", "UDUQREHMCEOSOQPN");
                // The Inbox folder is always available on all IMAP servers...
                var inbox = client.Inbox;
                inbox.Open(FolderAccess.ReadOnly);

               
                for (var i = 0; i < inbox.Count; i++)
                {
                    var message = await inbox.GetMessageAsync(i);
                    emails.Add(new Email
                    {
                        From = message.From.Mailboxes.FirstOrDefault()?.Address,
                        Subject = message.Subject,
                        Body = message.HtmlBody,     // Use HtmlBody instead of TextBody
                        Date = message.Date
                    });
                }

                client.Disconnect(true);
            }
            return Ok(emails);
        }
        [HttpGet("GetMails2")]
        public async Task<IActionResult> GetMails2()
        {
            string host = "imap.163.com";
            int port = 993; // for SSL
            string email = "zhangteng0208@163.com";
            string password = "UDUQREHMCEOSOQPN";

            using (var client = new ImapClient())
            {
                var clientImplementation = new ImapImplementation
                {
                    Name = "MailKitDemo",
                    Version = "1.0.0"
                };
                var serverImplementation = client.Identify(clientImplementation);

                client.ServerCertificateValidationCallback = (s, c, h, e) => true;

                client.Connect(host, port, true);

                client.Authenticate(email, password);

                var inbox = client.Inbox;
                inbox.Open(FolderAccess.ReadOnly);

                Console.WriteLine("Total messages: {0}", inbox.Count);

                client.Disconnect(true);

                return Ok("Total messages: "+ inbox.Count);
            
            }

           
        }

        [HttpGet("GetleokcAll")]
        public async Task<IActionResult> GetleokcAll()
        {
            var result = await db.Queryable<leokc>().GroupBy(it => new { it.usercode }).ToListAsync();

            var r = new ResultList<leokc>()
            {
                code = 200,
                msg = "OK",
                data = result
            };

            return Ok(r);

        }
        [HttpGet("GetPoemAsync")]
        public async Task<IActionResult> GetPoemAsync()
        {
            var request = new HttpRequestMessage(HttpMethod.Get,
                "https://v1.jinrishici.com/all.json");

            var client = _httpClientFactory.CreateClient();

            var response = await client.SendAsync(request);

            if (response.IsSuccessStatusCode)
            {
                return Content(await response.Content.ReadAsStringAsync(), "application/json");
            }
            else
            {
                return BadRequest();
            }
        }
        [HttpGet("Getjscode2session")]
        public async Task<IActionResult> Getjscode2session(string d,string iv,string code)
        {
            var s = utils.jscode2session("wxfb02cc61a6ef3971", "fabd1914b14d949a04b1041883a4ee3b", code, d, iv);

          

            return Ok(s);
        }
    }
    public class Result
    {
        public int code { get; set; }
        public string msg { get; set; }
    }

    public class ResultData<T> : Result
    {
        public T data { get; set; }
    }
    public class ResultList<T> : Result
    {
        public List<T> data { get; set; }
    }
    /// <summary>
    /// 查询实体
    /// </summary>
    public class QueryRequest
    {
        /// <summary>
        /// 查询sql语句
        /// </summary>
        public string? SqlQuery { get; set; }
    }
    /// <summary>
    /// 操作实体
    /// </summary>
    public class CommandRequest
    {
        /// <summary>
        /// 操作sql语句
        /// </summary>
        public string? SqlCommand { get; set; }
    }
   


    /// <summary>
    /// 表格合并行实体
    /// </summary>
    public class MergedCell
    {
        /// <summary>
        /// 开始行
        /// </summary>
        public int StartRow { get; set; }
        /// <summary>
        /// 结束行
        /// </summary>
        public int EndRow { get; set; }
        /// <summary>
        /// 开始列
        /// </summary>
        public int StartColumn { get; set; }
        /// <summary>
        /// 结束列
        /// </summary>
        public int EndColumn { get; set; }
        /// <summary>
        /// 值
        /// </summary>
        public string? Value { get; set; }
    }
}
